package com.itdct.onflow.generator.core

import cn.hutool.core.io.FileUtil
import com.itdct.onflow.core.extend.logError
import com.itdct.onflow.core.extend.logInfo
import com.itdct.onflow.core.util.TimeUtil
import com.itdct.onflow.generator.config.GeneratorConfig
import com.itdct.onflow.generator.context.GeneratorContext
import com.itdct.onflow.generator.model.ClassModel
import java.io.File
import java.nio.charset.Charset
import java.sql.Connection
import java.sql.DriverManager
import java.sql.Statement
import java.util.*

/**
 * @author Zhouwx
 * @date 2025/8/13 17:43:10
 * @version 1.0
 * @description
 */
class DbConnector(val generatorConfig: GeneratorConfig) {
    fun getConnection(): Connection {
        Class.forName("com.mysql.cj.jdbc.Driver")
        val url =
            "jdbc:mysql://${generatorConfig.databaseIp}:${generatorConfig.databasePort}/${generatorConfig.databaseName}?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&transformedBitIsBoolean=true&allowMultiQueries=true&tinyInt1isBit=false&allowPublicKeyRetrieval=true&useSSL=false&allowLoadLocalInfile=true&allow"
        val connection = DriverManager.getConnection(url, generatorConfig.databaseUserName, generatorConfig.databasePassword)
        return connection
    }

    fun syncTable(context: GeneratorContext, connection: Connection) {
        val classModel = context.classModel
        val statement = connection.createStatement()
        try {
            doSyncTable(classModel, statement, context)
        } catch (e: Exception) {
            logError("表${classModel.tableName}同步失败", e)
        } finally {
            statement.close()
        }
    }

    private fun doSyncTable(classModel: ClassModel, statement: Statement, context: GeneratorContext) {
        val tableName = classModel.tableName
        val checkExist = "show tables like '${tableName}'"
        val rs = statement.executeQuery(checkExist)
        if (!rs.next()) {
            val createSql = """
CREATE TABLE `${tableName}` (
${setLines(classModel)}
) ENGINE=InnoDB AUTO_INCREMENT=${classModel.autoIncrement} DEFAULT CHARSET=${classModel.charset} COLLATE=${classModel.collate} ROW_FORMAT=DYNAMIC COMMENT='${classModel.classUsage}';
                """.trimIndent()
            println(createSql)
            statement.executeUpdate(createSql)
            appendSql(createSql, context)
        } else {
            logInfo("表${tableName}已存在，进行表变更判断")
            val sqlList = ArrayList<String>()
            context.compareResultModel?.also {
                for (addField in it.addFields) {
                    var addIndex = 0
                    classModel.fieldList.forEachIndexed { index, fieldModel ->
                        if (fieldModel == addField) {
                            addIndex = index
                        }
                    }
                    val preField = classModel.fieldList[addIndex - 1]
                    val column = "ALTER TABLE `${tableName}` ADD COLUMN ${addField.ddl} AFTER `${preField.columnName}`;"
                    sqlList.add(column)
                }
                for (delField in it.delFields) {
                    // INFO: Zhouwx: 2025/8/19 删除字段全部采用注释的方式，防止真执行后删了不该删除的数据
                    sqlList.add("-- ALTER TABLE `${tableName}` DROP COLUMN `${delField.columnName}`;")
                }
                for (modifyPair in it.modifyPairs) {
                    val modifyField = modifyPair.second
                    sqlList.add("ALTER TABLE `${tableName}` MODIFY COLUMN ${modifyField.ddl.trim()};")
                }
            }
            if (sqlList.isNotEmpty()) {
                val sqlString = sqlList.joinToString("\n")
                logInfo("表${tableName}有变更，进行变更，SQL为：\n${sqlString}")
                statement.executeUpdate(sqlString)
                appendSql(sqlString, context)
            } else {
                logInfo("表${tableName}结构无变更")
            }
        }
    }

    private fun setLines(classModel: ClassModel): String {
        val sqlLines = ArrayList<String>()
        for (fieldModel in classModel.fieldList) {
            if (fieldModel.virtual) {
                continue
            }
            val column = fieldModel.ddl
            sqlLines.add(column)
        }

        for (fieldModel in classModel.fieldList) {
            if (fieldModel.virtual) {
                continue
            }

            if (fieldModel.primaryKey) {
                val primaryKey = "  PRIMARY KEY (`${fieldModel.columnName}`) USING BTREE"
                sqlLines.add(primaryKey)
            }

            if (fieldModel.indexed) {
                val index = "  KEY `${fieldModel.columnName}_idx` (`${fieldModel.columnName}`) USING BTREE"
                sqlLines.add(index)
            }

            if (fieldModel.unique) {
                val unique = "  UNIQUE KEY `${fieldModel.columnName}_uk` (`${fieldModel.columnName}`${logicDeleteColumn(classModel)}) USING BTREE"
                sqlLines.add(unique)
            }

            if (fieldModel.relateIndex.isNotEmpty()) {
                val indexName = fieldModel.relateIndex.joinToString("_") + "_idx"
                val indexColumns = "`" + fieldModel.columnName + "`," + fieldModel.relateIndex.map { "`$it`" }.joinToString(",")
                val index = "  KEY `$indexName` ($indexColumns) USING BTREE"
                sqlLines.add(index)
            }

            if (fieldModel.relateUnique.isNotEmpty()) {
                val uniqueName = fieldModel.relateUnique.joinToString("_") + "_uk"
                val uniqueColumns = "`" + fieldModel.columnName + "`," + fieldModel.relateUnique.map { "`$it`" }.joinToString(",") + logicDeleteColumn(classModel)
                val unique = "  UNIQUE KEY `$uniqueName` ($uniqueColumns) USING BTREE"
                sqlLines.add(unique)
            }
        }
        return sqlLines.joinToString(",\n")
    }

    private fun appendSql(sql: String, context: GeneratorContext) {
        if (!generatorConfig.generateSql) {
            return
        }

        val sqlFilePath = context.sqlFilePath
        val classModel = context.classModel
        val sqlFile = File(sqlFilePath)
        val infoSql = """
-- OnflowGenerator: 更新类：${classModel.fullClassName}
-- 更新时间：${TimeUtil.dateToTimeString(Date(), TimeUtil.yyyyMMddHHmmss_HYPHEN)}，更新人：${generatorConfig.authorName}
${sql}

        """.trimIndent()
        FileUtil.appendString(infoSql, sqlFile, Charset.defaultCharset())
    }

    private fun logicDeleteColumn(classModel: ClassModel): String {
        if (!classModel.haveLogicDelete) {
            return ""
        }
        return ",`${classModel.logicDeleteFieldName}`"
    }

}